PostgreSQL

Parameters tuned

The parameters tuned by DBtune changed in respect to the tuning mode. Below are the three tuning modes we support.

Reload-only tuning mode

This tuning mode tunes without restarting the database and using reload only. This does not cause any downtime and is suitable for production environments. The following parameters are tuned in this mode:

1. work_mem
2. random_page_cost
3. seq_page_cost
4. checkpoint_completion_target
5. effective_io_concurrency
6. max_parallel_workers_per_gather
7. max_parallel_workers
8. max_wal_size
9. min_wal_size
10. bgwriter_lru_maxpages
11. bgwriter_delay

Restart tuning mode

Most of our user base prefer reload-only tuning to avoid disruptions in production. If your system can handle restarts gracefully and you want to tune more parameters, you can chose this mode which restarts the database up to 30 times in a span of few hours and provide you with additional performance. The following parameters are tuned in this mode in addition to the parameters tuned in the reload-only mode:

12. shared_buffers
13. max_worker_processes

Optimization objectives

Average Query Runtime

Average query runtime represents latency, which is measured in milliseconds ms. DBtune computes this using the calls and total_exec_time columns from the pg_stat_statements table.

Transactions per second (TPS)

Throughput is defined as the number of transactions that the database completes successfully. DBtune computes PostgreSQL's throughput from the xact_commit metric in the pg_stat_database statistics table.

Agent monitoring stats

info

DBtune only retrieves performance metrics from the database and does not access or transmit any sensitive data, e.g., metadata and the tables data are not transferred.

You can find more information about the agent monitoring stats in our public Notion page.

System monitoring (posted every 5 seconds)

Below is the data DBtune collects and sends to the DBtune server every second:

Collected data

Category	Subcategory
CPU stats	cpu_util
Memory stats	free
	slab
	used
	total
	active
	cached
	shared
	buffers
	percent
	inactive
	available
IO stats	busy_time
	read_time
	read_bytes
	read_count
	write_time
	write_bytes
	write_count
	read_merged_count
	write_merged_count
DB Stats	Throughput
	Query Runtime

System information (posted every minute)

In less frequent intervals DBtune fetches the following data to understand the specifications of the system:

Collected data

Category	Subcategory
Hardware	Number of CPUs
	Total memory
	Available memory
	Cloud provider
	Instance type
	Disk type
Software	Database version
	Operating system type
	Maximum connections
	Database size